Create a data-bound Spreadsheet Web Part

1. Create a blank worksheet in Microsoft Excel.
2. On the Data menu, point to Import External Data, and then click Import Data.
3. In the Select Data Source dialog box, click New Source.
4. Do one of the following:
   • Select Microsoft Business Solutions, and then click Next.
   • Select Data retrieval services, click Next, select one of the data retrieval service data sources, such as Microsoft SQL Server or Microsoft SharePoint lists, and then click Next .
5. Type the URL for the Windows SharePoint Services server where you will deploy your Spreadsheet Web Part, and then click Next.
6. Type the server name and specify logon credentials, and then click Next.
7. Double-click the folder of the database that you want to retrieve data from (for example, Northwind), click the table, query, or other data object that you want to work with, and then click Next.
8. Select the columns that you want to work with, and then click Next.

   Note  If you want to make a connectable Web Part, you should include a column that can be used to establish a one-to-many relationship, such as an ID column.

9. Select the columns you want to sort by, and then click Next.
10. Specify the filter criteria (if any), and then click Next.
11. Choose whether to limit the number of rows returned, and then click Next.
12. Type the name for your Data Retrieval Service Connections file (.uxdc), and then click Finish.
13. Select the Data Retrieval Service Connections file (.uxdc) that you created in the preceding steps, and then click Open.
14. Import the data as an XML List into the current worksheet, starting at cell A1.

1. Hide the column containing the ID field.
2. To add a formula, insert a column into your XML List, type a formula into the first cell of that column, and then double-click the fill handle to copy the formula to all rows in the list.
3. To make the XML List headers visible when scrolling, select the row below the headers, and then click Freeze Panes on the Window menu.
4. Select and format cells in the first row of the XML list to display decimal places, currency symbols, fonts, and so on.
5. To hide the Excel row and column headers and horizontal scrollbar, click Options on the Tools menu, and on the View tab clear the Row & column headers and Horizontal scroll bar check boxes.
6. On the File menu, click Save As, and then navigate to a document library on the Windows SharePoint Services server where you will be using the Spreadsheet Web Part, set Save as type to XML Spreadsheet (*.xml), type a file name, and then click OK.

Note  You can also save the XML Spreadsheet file for your Spreadsheet Web Part locally and upload it to a document library later.

1. Start Notepad and create an .xml file in the following format:

   <?xml version="1.0" ?>
   <SolutionSpecification>
      <WebPartSettings>
         <XMLSSFileLocation>URLToXMLSpreadsheetFile
            </XMLSSFileLocation>
         <LockedDown>True</LockedDown>
      </WebPartSettings>
   </SolutionSpecification>

   Replacing URLToXMLSpreadsheetFile with the URL to the XML Spreadsheet file you created in the previous steps. This will typically be in a format like:

   
   http://ServerName/DocumentLibraryName/XMLSpreadsheetName.xml
   

   
   Or a relative path that omits the server name:

   
   /DocumentLibraryName/XMLSpreadsheetName.xml
   

2. Save the solution specification file with an .xml extension directly to a document library on your Windows SharePoint Services server, or save the file locally and upload it to the document library.

1. Start Notepad and create a Web Part Definition file (.dwp) in the following format:

   <?xml version="1.0"?>
   <WebPart
   xmlns="http://schemas.microsoft.com/WebPart/v2"
   xmlns:ODP="http://schemas.microsoft.com/WebPart/v2/Spreadsheet">
      <Assembly>Microsoft.Office.DataParts,Version=11.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c</Assembly>
      <TypeName>Microsoft.Office.DataParts.SpreadsheetCtl</TypeName>
      <Title>WebPartTitle</Title>
      <Description>WebPartDescription</Description>
      <ODP:SolutionFileLocation>URLToSolutionSpecificationFile
         </ODP:SolutionFileLocation>
   </WebPart>
   

   Important  To work correctly, the line that begins with <Assembly> and ends with </Assembly> must be formatted as a single line with no line breaks or spaces. If you add spaces or line breaks between attributes, importing the Spreadsheet Web Part will fail.

   The following table describes what to enter for each of the italicized items.

   Item	Description
   WebPartTitle	The title for your Web Part.
   WebPartDescription	The description for your Web Part.
   URLToSolutionSpecificationFile	The URL to the solution specification file (.xml) you created in the previous steps. This will typically be in a format like: http://ServerName/DocumentLibraryName/SolutionSpecificationFileName.xml

2. Save the file with a .dwp extension.
3. Create a new Web Part page (on the Windows SharePoint Services toolbar click Create, and then under Web Pages, click Web Part Page).
4. Click Modify My Page or Modify Shared Page (depending on whether you are viewing the page in Personal View or Shared View), and then click Add Web Parts.
5. Click Import, specify the .dwp file you created in the previous steps, and then click Upload.
6. Drag the Web Part to the zone where you want your Spreadsheet Web Part to appear.
7. Close the tool pane.

Note  If you are an administrator of a Windows SharePoint Services site or server, you can also import the Web Part definition file for a Spreadsheet Web Part into a Web Part gallery to make it available to other users of the site or server.